package net.lab1024.sa.admin.module.business.datasource.service.impl;

import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import lombok.extern.slf4j.Slf4j;
import net.lab1024.sa.admin.module.business.datasource.dao.DatasourceDao;
import net.lab1024.sa.admin.module.business.datasource.dao.QueryHistoryDao;
import net.lab1024.sa.admin.module.business.datasource.domain.entity.DatasourceEntity;
import net.lab1024.sa.admin.module.business.datasource.domain.entity.QueryHistoryEntity;
import net.lab1024.sa.admin.module.business.datasource.domain.form.QueryHistoryQueryForm;
import net.lab1024.sa.admin.module.business.datasource.domain.form.SqlExecuteForm;
import net.lab1024.sa.admin.module.business.datasource.domain.vo.DatabaseObjectVO;
import net.lab1024.sa.admin.module.business.datasource.domain.vo.QueryHistoryVO;
import net.lab1024.sa.admin.module.business.datasource.domain.vo.QueryResultVO;
import net.lab1024.sa.admin.module.business.datasource.service.DatabaseManagerService;
import net.lab1024.sa.base.common.code.UserErrorCode;
import net.lab1024.sa.base.common.domain.ResponseDTO;
import net.lab1024.sa.base.common.util.SmartRequestUtil;
import org.springframework.stereotype.Service;

import jakarta.annotation.Resource;
import java.sql.*;
import java.util.*;

/**
 * 数据库管理Service实现
 *
 * @Author 1024创新实验室-主任:卓大
 * @Date 2024-01-01 00:00:00
 * @Wechat zhuoda1024
 * @Email lab1024@163.com
 * @Copyright <a href="https://1024lab.net">1024创新实验室</a>
 */
@Slf4j
@Service
public class DatabaseManagerServiceImpl implements DatabaseManagerService {

    @Resource
    private DatasourceDao datasourceDao;

    @Resource
    private QueryHistoryDao queryHistoryDao;

    @Override
    public ResponseDTO<List<DatabaseObjectVO>> getDatabaseObjects(Long datasourceId, String objectType) {
        try {
            // 获取数据源信息
            DatasourceEntity datasource = datasourceDao.selectById(datasourceId);
            if (datasource == null) {
                return ResponseDTO.error(UserErrorCode.DATA_NOT_EXIST, "数据源不存在");
            }

            // 连接数据库
            try (Connection connection = getConnection(datasource)) {
                List<DatabaseObjectVO> objects = new ArrayList<>();
                
                // 获取数据库元数据
                DatabaseMetaData metaData = connection.getMetaData();
                
                if ("table".equals(objectType)) {
                    // 获取表信息
                    ResultSet tables = metaData.getTables(datasource.getDatabaseName(), null, "%", new String[]{"TABLE"});
                    while (tables.next()) {
                        DatabaseObjectVO object = new DatabaseObjectVO();
                        object.setDatasourceId(datasourceId);
                        object.setObjectType("table");
                        object.setObjectName(tables.getString("TABLE_NAME"));
                        object.setObjectComment(tables.getString("REMARKS"));
                        objects.add(object);
                    }
                } else if ("view".equals(objectType)) {
                    // 获取视图信息
                    ResultSet views = metaData.getTables(datasource.getDatabaseName(), null, "%", new String[]{"VIEW"});
                    while (views.next()) {
                        DatabaseObjectVO object = new DatabaseObjectVO();
                        object.setDatasourceId(datasourceId);
                        object.setObjectType("view");
                        object.setObjectName(views.getString("TABLE_NAME"));
                        object.setObjectComment(views.getString("REMARKS"));
                        objects.add(object);
                    }
                }
                
                return ResponseDTO.ok(objects);
            }
        } catch (Exception e) {
            log.error("获取数据库对象失败", e);
            return ResponseDTO.error(UserErrorCode.PARAM_ERROR, "获取数据库对象失败: " + e.getMessage());
        }
    }

    @Override
    public ResponseDTO<QueryResultVO> executeQuery(SqlExecuteForm form) {
        long startTime = System.currentTimeMillis();
        QueryResultVO result = new QueryResultVO();
        
        try {
            // 获取数据源信息
            DatasourceEntity datasource = datasourceDao.selectById(form.getDatasourceId());
            if (datasource == null) {
                return ResponseDTO.error(UserErrorCode.DATA_NOT_EXIST, "数据源不存在");
            }

            // 连接数据库
            try (Connection connection = getConnection(datasource)) {
                // 检查SQL类型
                String sql = form.getSql().trim().toLowerCase();
                ResponseDTO<QueryResultVO> response;
                if (sql.startsWith("select")) {
                    response = executeSelectQuery(connection, form, result, startTime);
                } else {
                    response = executeUpdateQuery(connection, form, result, startTime);
                }
                
                // 保存查询历史
                saveQueryHistory(form.getDatasourceId(), form.getSql(), 
                    result.getExecutionTime() != null ? result.getExecutionTime().intValue() : 0, 
                    result.getTotal() != null ? result.getTotal().intValue() : 0,
                    result.getSuccess() ? 1 : 0, result.getErrorMessage());
                
                return response;
            }
        } catch (Exception e) {
            log.error("执行SQL查询失败", e);
            result.setSuccess(false);
            result.setErrorMessage(e.getMessage());
            result.setExecutionTime(System.currentTimeMillis() - startTime);
            return ResponseDTO.ok(result);
        }
    }

    @Override
    public ResponseDTO<QueryResultVO> executeUpdate(SqlExecuteForm form) {
        long startTime = System.currentTimeMillis();
        QueryResultVO result = new QueryResultVO();
        
        try {
            // 获取数据源信息
            DatasourceEntity datasource = datasourceDao.selectById(form.getDatasourceId());
            if (datasource == null) {
                return ResponseDTO.error(UserErrorCode.DATA_NOT_EXIST, "数据源不存在");
            }

            // 连接数据库
            try (Connection connection = getConnection(datasource)) {
                ResponseDTO<QueryResultVO> response = executeUpdateQuery(connection, form, result, startTime);
                
                // 保存查询历史
                saveQueryHistory(form.getDatasourceId(), form.getSql(), 
                    result.getExecutionTime() != null ? result.getExecutionTime().intValue() : 0,
                    result.getAffectedRows() != null ? result.getAffectedRows() : 0,
                    result.getSuccess() ? 1 : 0, result.getErrorMessage());
                
                return response;
            }
        } catch (Exception e) {
            log.error("执行SQL更新失败", e);
            result.setSuccess(false);
            result.setErrorMessage(e.getMessage());
            result.setExecutionTime(System.currentTimeMillis() - startTime);
            return ResponseDTO.ok(result);
        }
    }

    @Override
    public ResponseDTO<QueryResultVO> getTableStructure(Long datasourceId, String tableName) {
        try {
            // 获取数据源信息
            DatasourceEntity datasource = datasourceDao.selectById(datasourceId);
            if (datasource == null) {
                return ResponseDTO.error(UserErrorCode.DATA_NOT_EXIST, "数据源不存在");
            }

            // 连接数据库
            try (Connection connection = getConnection(datasource)) {
                QueryResultVO result = new QueryResultVO();
                List<QueryResultVO.ColumnInfo> columns = new ArrayList<>();
                List<Map<String, Object>> rows = new ArrayList<>();

                // 获取表结构信息
                DatabaseMetaData metaData = connection.getMetaData();
                ResultSet columnRs = metaData.getColumns(datasource.getDatabaseName(), null, tableName, "%");
                
                while (columnRs.next()) {
                    QueryResultVO.ColumnInfo column = new QueryResultVO.ColumnInfo();
                    column.setName(columnRs.getString("COLUMN_NAME"));
                    column.setLabel(columnRs.getString("COLUMN_NAME"));
                    column.setType(columnRs.getString("TYPE_NAME"));
                    columns.add(column);
                    
                    Map<String, Object> row = new HashMap<>();
                    row.put("columnName", columnRs.getString("COLUMN_NAME"));
                    row.put("dataType", columnRs.getString("TYPE_NAME"));
                    row.put("columnSize", columnRs.getInt("COLUMN_SIZE"));
                    row.put("nullable", columnRs.getInt("NULLABLE") == DatabaseMetaData.columnNullable);
                    row.put("defaultValue", columnRs.getString("COLUMN_DEF"));
                    row.put("remarks", columnRs.getString("REMARKS"));
                    rows.add(row);
                }
                
                result.setColumns(columns);
                result.setRows(rows);
                result.setSuccess(true);
                result.setTotal((long) rows.size());
                
                return ResponseDTO.ok(result);
            }
        } catch (Exception e) {
            log.error("获取表结构失败", e);
            return ResponseDTO.error(UserErrorCode.PARAM_ERROR, "获取表结构失败: " + e.getMessage());
        }
    }

    @Override
    public ResponseDTO<Page<QueryHistoryVO>> getQueryHistory(QueryHistoryQueryForm form) {
        Page<QueryHistoryVO> page = new Page<>(form.getPageNum(), form.getPageSize());
        Page<QueryHistoryVO> result = queryHistoryDao.queryPage(page, form.getDatasourceId(), 
            SmartRequestUtil.getRequestUserId(), form.getKeyword());
        return ResponseDTO.ok(result);
    }

    @Override
    public void saveQueryHistory(Long datasourceId, String sqlContent, Integer executionTime, 
                                Integer rowCount, Integer status, String errorMessage) {
        try {
            QueryHistoryEntity entity = new QueryHistoryEntity();
            entity.setDatasourceId(datasourceId);
            entity.setSqlContent(sqlContent);
            entity.setExecutionTime(executionTime);
            entity.setRowCount(rowCount);
            entity.setStatus(status);
            entity.setErrorMessage(errorMessage);
            entity.setUserId(SmartRequestUtil.getRequestUserId());
            
            queryHistoryDao.insert(entity);
        } catch (Exception e) {
            log.error("保存查询历史失败", e);
        }
    }

    /**
     * 获取数据库连接
     */
    private Connection getConnection(DatasourceEntity datasource) throws Exception {
        String url = buildJdbcUrl(datasource);
        Class.forName(getDriverClassName(datasource.getType()));
        return DriverManager.getConnection(url, datasource.getUsername(), datasource.getPassword());
    }

    /**
     * 执行SELECT查询
     */
    private ResponseDTO<QueryResultVO> executeSelectQuery(Connection connection, SqlExecuteForm form, QueryResultVO result, long startTime) throws SQLException {
        List<QueryResultVO.ColumnInfo> columns = new ArrayList<>();
        List<Map<String, Object>> rows = new ArrayList<>();
        
        try (PreparedStatement stmt = connection.prepareStatement(form.getSql())) {
            ResultSet rs = stmt.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            
            // 获取列信息
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                QueryResultVO.ColumnInfo column = new QueryResultVO.ColumnInfo();
                column.setName(metaData.getColumnName(i));
                column.setLabel(metaData.getColumnLabel(i));
                column.setType(metaData.getColumnTypeName(i));
                column.setWidth(metaData.getColumnDisplaySize(i));
                columns.add(column);
            }
            
            // 获取数据行
            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    String columnName = metaData.getColumnName(i);
                    Object value = rs.getObject(i);
                    row.put(columnName, value);
                }
                rows.add(row);
            }
        }
        
        result.setColumns(columns);
        result.setRows(rows);
        result.setSuccess(true);
        result.setTotal((long) rows.size());
        result.setExecutionTime(System.currentTimeMillis() - startTime);
        
        return ResponseDTO.ok(result);
    }

    /**
     * 执行UPDATE查询
     */
    private ResponseDTO<QueryResultVO> executeUpdateQuery(Connection connection, SqlExecuteForm form, QueryResultVO result, long startTime) throws SQLException {
        try (PreparedStatement stmt = connection.prepareStatement(form.getSql())) {
            int affectedRows = stmt.executeUpdate();
            
            result.setSuccess(true);
            result.setAffectedRows(affectedRows);
            result.setExecutionTime(System.currentTimeMillis() - startTime);
            
            return ResponseDTO.ok(result);
        }
    }

    /**
     * 构建JDBC URL
     */
    private String buildJdbcUrl(DatasourceEntity datasource) {
        switch (datasource.getType().toLowerCase()) {
            case "mysql":
                return String.format("jdbc:mysql://%s:%d/%s?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true",
                        datasource.getHost(), datasource.getPort(), datasource.getDatabaseName());
            case "oracle":
                return String.format("jdbc:oracle:thin:@%s:%d:%s",
                        datasource.getHost(), datasource.getPort(), datasource.getDatabaseName());
            case "postgresql":
                return String.format("jdbc:postgresql://%s:%d/%s",
                        datasource.getHost(), datasource.getPort(), datasource.getDatabaseName());
            case "sqlserver":
                return String.format("jdbc:sqlserver://%s:%d;databaseName=%s",
                        datasource.getHost(), datasource.getPort(), datasource.getDatabaseName());
            default:
                throw new IllegalArgumentException("不支持的数据库类型: " + datasource.getType());
        }
    }

    /**
     * 获取驱动类名
     */
    private String getDriverClassName(String type) {
        switch (type.toLowerCase()) {
            case "mysql":
                return "com.mysql.cj.jdbc.Driver";
            case "oracle":
                return "oracle.jdbc.driver.OracleDriver";
            case "postgresql":
                return "org.postgresql.Driver";
            case "sqlserver":
                return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
            default:
                throw new IllegalArgumentException("不支持的数据库类型: " + type);
        }
    }
} 